The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have several columns. Column1 contains 'category'. Column2 contains sub category showing yes and no options. Column 3 has sub sub category showing Y and N option. Column 4 has IDs. I want to have top 10 categories based on distinct count of IDs filtered 'yes' for sub category and Y for sub sub category.
There is also a coloumn which shows grades.The table must contain grades column but the top 10 caculation shoould be based on the distinct count of IDs using the filters and not based on grades too. Can somebody please help me with this complex issue. Many thanks
Solved! Go to Solution.
Hi @SB13 ,
Please try the following methods and check if they can solve your problem:
1.Create the simple table.
2.Create the new column to filter yes for sub category and Y for sub sub category. Add the field to filter pane and set the value is 1.
yes_Y_category = IF(AND('Table'[sub category] = "yes", 'Table'[sub sub category] = "Y"), 1, 0)
3.Create the measure to distinct ID.
Discou_ID = SUMX('Table', DISTINCTCOUNT('Table'[ID]))
4.Create the measure to rank.
Rank_categ = RANKX(ALL('Table'[Category]), [Discou_ID],,DESC,Dense)
5.Drag the measure into the table visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @SB13 ,
Please try the following methods and check if they can solve your problem:
1.Create the simple table.
2.Create the new column to filter yes for sub category and Y for sub sub category. Add the field to filter pane and set the value is 1.
yes_Y_category = IF(AND('Table'[sub category] = "yes", 'Table'[sub sub category] = "Y"), 1, 0)
3.Create the measure to distinct ID.
Discou_ID = SUMX('Table', DISTINCTCOUNT('Table'[ID]))
4.Create the measure to rank.
Rank_categ = RANKX(ALL('Table'[Category]), [Discou_ID],,DESC,Dense)
5.Drag the measure into the table visual. The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @SB13,
Can you please try this approach:
1. Create a Measure for Distinct Count of IDs
DistinctCountFilteredIDs =
CALCULATE(
DISTINCTCOUNT(YourTable[ID]),
YourTable[SubCategory] = "yes",
YourTable[SubSubCategory] = "Y"
)
2. Create a Measure for Ranking Categories
RankCategories =
RANKX(
ALL(YourTable[Category]),
[DistinctCountFilteredIDs],
, DESC, Dense
)