Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello all,
I have this table with multiple same entries. I can't just delete the multiple entries cause I need to count them for a visual later.
Anyway, I also needed their a table with their distinct count. So I made a distincttable using:
Solved! Go to Solution.
Hi @Anonymous ,
LOOKUPVALUE() function could not return more than one distinct value. As @Greg_Deckler suggested, you could use MAXX() instead. Or SUMX(), FIRSTNONBLANK() and so on like this:
Distincttable =
ADDCOLUMNS (
DISTINCT ( 'UNION'[Sales Doc.] ),
"MAX Net Value",
MAXX (
FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
[Net value]
),
"SUM Net Value",
SUMX (
FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
[Net value]
),
"FIRST Net Value", FIRSTNONBLANK ( 'UNION'[Net value], 1 )
)
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
LOOKUPVALUE() function could not return more than one distinct value. As @Greg_Deckler suggested, you could use MAXX() instead. Or SUMX(), FIRSTNONBLANK() and so on like this:
Distincttable =
ADDCOLUMNS (
DISTINCT ( 'UNION'[Sales Doc.] ),
"MAX Net Value",
MAXX (
FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
[Net value]
),
"SUM Net Value",
SUMX (
FILTER ( 'UNION', 'UNION'[Sales Doc.] = EARLIER ( 'UNION'[Sales Doc.] ) ),
[Net value]
),
"FIRST Net Value", FIRSTNONBLANK ( 'UNION'[Net value], 1 )
)
The final output is shown below:
Please take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-eqin-msft Evelyn for the great support!
I had to play around with the expression a little but it looked like it worked...
I mean it does a good job and picks all the Sales Docs.& it also looks like it retrieves the correspondign Net Values but unfortuntely not all as it seems... I aggregated the values manually in the raw tables and the number PowerBI gives me is roughly 4 times the number it should be and I have absolutely no idea why that is...!
And yeah, I double-checked my measures as well as the Excel-functions I used to calculate by hand...
@Anonymous - It's difficult to say honestly because I don't feel like I really understand your data, data model, etc. If you could potentially post just a bit of sample data as text, expected output and basically explain how to get from a to b that would be super helpful.
Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@Anonymous Try using the MAXX(FILTER(...),...) method instead of LOOKUPVALUE. I have seen LOOKUPVALUE return bizarre results in various situations but MAXX(FILTER(...),...) never fails.
Thanks for the tip Greg!
So I tried using:
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |