The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello Everyone,
I am a new to Power BI, I need a help for Dax , I have matrix table , table image is given below
so , I want to top 1 GICS Level name using High Banchmark End Weight , want result is like ('Financials') bacause in financial Banchmark End Weight value is higher the others , how can I get value of top 1 GICS Level
Can anyone help me ?
and yes I also tried with this
CALCULATE([EndWeightBanchmark],FILTER(VALUES(ContributionCategory[SECTOR]),RANKX(ALL(ContributionCategory[SECTOR]),[EndWeightBanchmark],,DESC)=1))
But I am getting 23.17 not name of GICS Level
Here, ContributionCategory[SECTOR] is GICS Level which is coming from ContributionCategory
and [EndWeightBanchmark] is Measure column
Solved! Go to Solution.
Try with
= CALCULATE ( MAX ( [GICS Level] ), FILTER ( VALUES ( ContributionCategory[SECTOR] ), RANKX ( ALL ( ContributionCategory[SECTOR] ), [EndWeightBanchmark],, DESC ) = 1 ) )
Try with
= CALCULATE ( MAX ( [GICS Level] ), FILTER ( VALUES ( ContributionCategory[SECTOR] ), RANKX ( ALL ( ContributionCategory[SECTOR] ), [EndWeightBanchmark],, DESC ) = 1 ) )
Hi
@Zubair_Muhammad, one more think for above example ,
How can I ignore blank value and whatever result got on second or third those rank start as first ,
For instance, we do have 10 values , out of it first three value is blank, and I want 4th value connsider as a first rank. How can I get it? Please do let me know ASAP.
I think you will need to modify the table parameter of RANKX
But not sure
RANKX(CALCULATETABLE(VALUES(ContributionCategory[SECTOR]),[VALUES]<>BLANK())
Hi,
@Zubair_MuhammadThanks for kind reply,
I tried with
here my fields and table are different but scenario is same , I m not getting value on first
rank , getting blank value on first rank
any other solution ?
Could you copy paste some sample data with expected results?
Hi
@Zubair_Muhammad
I found one more solution , I have recently checked the value, is not blank but it is nontext value.
so now I want to use ISNONTEXT() for check whether it is text or not. I want only text value rank by desc ,
so how can I use ISNONTEXT() in RANKX() ?
I tried but not getting proper result. so can you please help one more time?
here is code , which I have tried ,u can see
var first = CALCULATE(
MAX( Sect[Security Name]),
FILTER(
VALUES ( ContributionCategory ),
ContributionCategory[SECTOR] = thiredPortfolio
),
FILTER(Sect,
Sect[Security Name] <> BLANK()
),
FILTER (
VALUES( Sect[Security Name] ),
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
)
)
Here , I m trying to get top 1 with ignoring blank or empty
when I tried with RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 6
then I got my expected result but this is not perfact , bacause all the 1 to 5 ranks results is blank or empty ,
so i want ignore all five ... and ,
always I want my result in first rank(ignoring all blank or empty values)
Hi
@Zubair_Muhammad Thank you very much for kind reply , this is useful for mw , I got my result as i expected
Thanks again
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
75 | |
62 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |