Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IF
Post Prodigy
Post Prodigy

Hierarchy selection and result in a table and a card

Hi,

I have two tables:

SelectionTable:

RegionSubregionIDSubID
EuropeCenterA22 
EuropeCenterA123A22
EuropeCenterA134A22

 

Data:

MonthCountry_CityIDConducted
03.01.2021ParisA1230
04.01.2021ParisA1231
05.01.2021ParisA1232
04.01.2021LyonA1340
05.01.2021LyonA1340
05.01.2021FranceA223

 

I check Data[conducted]. if it is more than 0, I consider it.

I have 4 slicers to select region, subregion, ID and subID. If I select region=Europe, Subregion=center, ID=A22, the card /text should show: 2 out of 3.

In the table I should see:

ID
A123
A22

 

the id=A134 doesn't have a score of more than 0. therefore it is not included in the list.

 

If I select region=Europe, Subregion=center, ID=A22, SubID=A123, the card /text should show: 1 out of 1. In the table, I should see

ID
A123

 

thanks in advance!

1 ACCEPTED SOLUTION

Hi,  @IF 

In my attachment, I adjusted the data in your table. I don’t know if the result can meet your expectations.

16.png

Best Regards,
Community Support Team _ Eason

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@IF , Do you need a measure like

 

Countrows(Filter( Data, data[Conducted] >0 ))

Would it be possible to show it? I tried and it doesn't work based on the slicer selection "ID" and subID".

Hi, @IF 

Sorry, your explanation confuses me even more. What does Hierarchy selection mean? 

I did not find any row matching SubID=A123 in your selection table.

If I select region=Europe, Subregion=center, ID=A22, SubID=A123, the card /text should show: 1 out of 1.

How did you establish the relationship between 'Selection Table' and 'Data'?

If possible, please share a sample file for further research.

 

Best Regards,
Community Support Team _ Eason

Thanks for checking it. Actually, if I have the relationship between SelectionTable[ID] and Data[ID] then it provides 1/1 result. If I do the relationship between  SelectionTable[ID] and Data[ID]  then it provides 1/2 results.  I thought it would be best not to have an active relationship but write a dax to calculate the total which would be 2/3. I don't know if it would be possible to write such dax. I think there should not be an active relationship, otherwise, I will not get that result.

Hi, @IF 

Still a little confused. For example, you say 'If I have a relationship between SelectionTable[ID] and Data[ID], then it provides a 1/1 result. If I have a relationship between SelectionTable[ID] and Data[ID], then it provides 1/2 result.'  But it is difficult for me to understand how 1/2 is obtained (numerator 1 refers to which part of the filtered data, and denominator 2 refers to which part of the data). 

 

Best Regards,
Community Support Team _ Eason

It is a little bit difficult to explain. Because of the structure of the SelectionTable, it is not possible the select ID, which is "A22" and then select the SubID, which is"A123" in this case. I can rename SubID, it doesn't solve the problem.

111.jpg

I know my relationship doesn't work, but don't know what to do.

My desire is to select the ID as "A22" and then select the SubID, which is"A123" and see the result for "A123". If I only select ID="A22", then I want to see the possible subcategories for "A22".

Hi,  @IF 

In my attachment, I adjusted the data in your table. I don’t know if the result can meet your expectations.

16.png

Best Regards,
Community Support Team _ Eason

tx. wonderful!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.