cancel
Showing results for 
Search instead for 
Did you mean: 
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
v-easonf-msft
Community Support
Community Support

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 ))



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

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

v-easonf-msft
Community Support
Community Support

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.

v-easonf-msft
Community Support
Community Support

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".

v-easonf-msft
Community Support
Community Support

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors