Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |