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.
Hi ,
I have created below dax to filter value from two slicers.Issue is when i select "Active Users" from slicer [A] and "Active YoY" from slicer [B] then only Active Users show 1.I want both Active Users and Active YoY show 1.
CategoryActiveUsers = IF(SEARCH("Active Users",MAXX(VALUES(Sheet5),Sheet5[Metric]),1,BLANK()),1,0) CategoryYoY = IF(SEARCH("Active YoY",MAXX(VALUES(Sheet5),Sheet5[Metric]),1,BLANK()),1,0) MeasureSelected = IF ( HASONEVALUE ( Metric[A] ) , SWITCH ( FIRSTNONBLANK ( Metric[A],Metric[A] ), "Active Users", [CategoryActiveUsers] ), IF( (HASONEVALUE(YoY[B])),SWITCH(FIRSTNONBLANK(YoY[B],YoY[B]),"Active YoY",[CategoryActiveYoY])) )
Solved! Go to Solution.
Hi @atul9063,
CategoryActiveUsers = IF ( ISERROR ( SEARCH ( SELECTEDVALUE ( Sheet5[Metric] ), IF ( ISFILTERED ( Metric[A] ), CONCATENATEX ( Metric, Metric[A], "," ), BLANK () ) ) ), 0, 1 ) CategoryYoY = IF ( ISERROR ( SEARCH ( SELECTEDVALUE ( Sheet5[Metric] ), IF ( ISFILTERED ( YoY[B] ), CONCATENATEX ( YoY, YoY[B], "," ), BLANK () ) ) ), 0, 1 ) MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)
Best regards,
Yuliana Gu
Hello,
I created a similar measure in my report and it works perfectly in a table where I list individual records as above. However, I need to create a bar chart (or table) that shows a disctinct count of records in a given category but as soon as I change from "Don't Summarize" to "Count (Distinct)", all of the data disappears. Anything I can do to fix that?
Thanks.
I was able to get what I need by adding the fields being filtered to the Axis (below my date field). They do not appear on the chart, but blank months are now showing.
Hi @atul9063,
The formula for measure [MeasureSelected] could be simplified to:
MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)
Best regards,
Yuliana Gu
Hi,
When you unselect Active YoY and Active Users,even then Measure selected showing 1. I want to show whenever value selected either from A or B,it should display 1 in measure selected
Hi @atul9063,
Please modify measures as below:
CategoryActiveUsers = IF ( ISFILTERED ( Metric[A] ) && NOT ( ISERROR ( SEARCH ( SELECTEDVALUE ( Metric[A] ), SELECTEDVALUE ( Sheet5[Metric] ) ) ) ), 1, 0 ) CategoryYoY = IF ( ISFILTERED ( YoY[B] ) && NOT ( ISERROR ( SEARCH ( SELECTEDVALUE ( YoY[B] ), SELECTEDVALUE ( Sheet5[Metric] ) ) ) ), 1, 0 ) MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)
Best regards,
Yuliana Gu
@v-yulgu-msft Thanks for replying back..
Now issue is coming,when I enable multiselect for both slicer and select only Active YoY and Idle YoY, then all four values showing 1.
Any help when i multi select only those values should show 1.
Hi @atul9063,
CategoryActiveUsers = IF ( ISERROR ( SEARCH ( SELECTEDVALUE ( Sheet5[Metric] ), IF ( ISFILTERED ( Metric[A] ), CONCATENATEX ( Metric, Metric[A], "," ), BLANK () ) ) ), 0, 1 ) CategoryYoY = IF ( ISERROR ( SEARCH ( SELECTEDVALUE ( Sheet5[Metric] ), IF ( ISFILTERED ( YoY[B] ), CONCATENATEX ( YoY, YoY[B], "," ), BLANK () ) ) ), 0, 1 ) MeasureSelected = IF([CategoryActiveUsers]||YoY[CategoryYoY],1,0)
Best regards,
Yuliana Gu
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |