Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Wonder if someone can help me.
I have the following ranking measure:
Rank Units =
IF (
HASONEVALUE ( 'Segment'[Segment] ),
RANKX (
ALLSELECTED ( 'Company'[Company] ),
'Analysis Measures'[Total Units],
,
DESC,
DENSE
),
BLANK ()
)
Which works very and allows me to make a table like below:
I need help with 2 things...
1) I want to remove "Others" from the ranking - This can be filtered out before the ranking done.
2) I want to create a measure which just returns the ranking in this table when Company="HP inc" so in this case "3", but must still work with the filters applied to the page report.
Thank you
Solved! Go to Solution.
@Anonymous
The code above works fine. The only problem is that you do not have a filter on 'Segment'[Segment] but rather on 'Segment'[Lenovo_Segment]. So, since we are checking whether HASONEVALUE ( 'Segment'[Segment] ) it always returns blank. So you can either:
a) Include the proper filter on 'Segment'[Segment]
b) Change the code to do the check on 'Segment'[Lenovo_Segment] instead:
Rank Units V2 =
IF (
HASONEVALUE ( 'Segment'[Lenovo_Segment] ),
RANKX (
FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
'Analysis Measures'[Total Units],
,
DESC,
DENSE
),
BLANK ()
)
c) Other options like getting rid of that check altogether.
Finally, do note that you are not filtering out "Others" on the visual table, although you are using it as reference
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
I dont' get a 4, I get a 3, so I'm not sure what you're doing. Be sure to apply a filter to the table visual excluding "Others" (or to the whole page) if you're to compare its results to those of the card.
Make sure you apply the geo filter to both table and card visuals; best use a slicer on Geography'[GEO] so that it applies to the whole page.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
I dont' get a 4, I get a 3, so I'm not sure what you're doing. Be sure to apply a filter to the table visual excluding "Others" (or to the whole page) if you're to compare its results to those of the card.
Make sure you apply the geo filter to both table and card visuals; best use a slicer on Geography'[GEO] so that it applies to the whole page.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
OK, I understand where I was going wrong.
Thank you so much for your help.... Sorry I am just learning DAX...
@Anonymous
The code above works fine. The only problem is that you do not have a filter on 'Segment'[Segment] but rather on 'Segment'[Lenovo_Segment]. So, since we are checking whether HASONEVALUE ( 'Segment'[Segment] ) it always returns blank. So you can either:
a) Include the proper filter on 'Segment'[Segment]
b) Change the code to do the check on 'Segment'[Lenovo_Segment] instead:
Rank Units V2 =
IF (
HASONEVALUE ( 'Segment'[Lenovo_Segment] ),
RANKX (
FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
'Analysis Measures'[Total Units],
,
DESC,
DENSE
),
BLANK ()
)
c) Other options like getting rid of that check altogether.
Finally, do note that you are not filtering out "Others" on the visual table, although you are using it as reference
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AIB,
Thank you, I have changed 'Segment'-
If I could ask one more thing:
And Sorry maybe I am not understanding the RankX function.
If I add a filter 'Geography'[GEO]= "China" straight away I get the wrong the ranking number.
How would I also add the GEO table so that this could also be filtered by.
@Anonymous
Rank Units V2 =
IF (
HASONEVALUE ( 'Segment'[Segment] ),
RANKX (
FILTER ( ALL ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
'Analysis Measures'[Total Units],
,
DESC,
DENSE
),
BLANK ()
)
If this doesn't work, I'd need to see the pbix (or a pbix with dummy data that reproduces your scenario)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB
Here is a link to download a copy of the PBIX.
https://1drv.ms/u/s!AtoYZpsBxPqIhsJwnIR3MtF3pcw0FA?e=lEh0Vv
I changed some of the data.
Also you will see on page one the item I am trying to solve for.
I want to replace the (BLANK) item in the blue box with the rank number.
TAHNK YOU
Hi @Anonymous
Rank Units =
VAR aux_ =
IF (
HASONEVALUE ( 'Segment'[Segment] ),
RANKX (
FILTER ( ALLSELECTED ( 'Company'[Company] ), 'Company'[Company] <> "Others" ),
'Analysis Measures'[Total Units],
,
DESC,
DENSE
),
BLANK ()
)
RETURN
IF ( aux_ = 3, aux_ )
You can also exclude "Others" with a filter on the visual/page, rather than hard-coding it into the measure
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I do not want to return the company name, I want to return the rank,
So:
IF Company = "HP inc" what is the rank?
@Anonymous
??? The code above does return a rank, doesn't it?
Please show the expected result
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thank you for your help...
Expected result is that I add to a card.
Then filter the visual Company="HP inc" Or this can be hard coded into the measure.
And result looks like below:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |