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.
In the given Image I have multiple Parent IDs. For some of the Parent ID there are common Child IDs are there. If I select two or more Parent ID , I should get only common Child ID Values using DAX
e.g. Parent 2107 and 2108 are having 78,88,97 common Child IDs . So I need to display only Common Child IDs using Dax
sample Input / Output
Solved! Go to Solution.
Hi @RahulShahane,
Since measures cannot output columns or tables, you need some trick.
For example, you can:
1. Create a table Children:
Children = DISTINCT ( data[Child] )
2. Set up a relationship with the main table.
3. Create a measure:
Flag =
VAR Count_of_parents = COUNT ( data[Parent] )
VAR Total_count = CALCULATE ( DISTINCTCOUNT ( data[Parent] ), REMOVEFILTERS ( 'Children' ) )
RETURN Total_count - Count_of_parents
4. Add a table visual with the field 'Children'[Child] and the measure [Flag]
5. Set a filter in the visual "[Flag] = 0"
6. (optional) Add a rectangle to hide the [Flag] column.
Here's the output (w/o the rectangle):
If somebody proposes a cuter solution, I'll be happy to upgrade my knowledge. 🙂
Hi,
I'd propose to solve your case with such a measure:
Intersection of children =
VAR Number_of_selected_parents = DISTINCTCOUNT( data[Parent] )
VAR Temp_table = FILTER ( SUMMARIZE (data, data[Child], "Qty of parents", COUNT ( data[Parent] ) ), [Qty of parents] = Number_of_selected_parents )
RETURN CONCATENATEX ( Temp_table, [Child], ", " )
Here's how it works:
Hi Barritown , This solution really worked for me. Thanks.
One more help, I need to show this coommon values in table column. Can u pls modify the measure for that.
Hi @RahulShahane,
Since measures cannot output columns or tables, you need some trick.
For example, you can:
1. Create a table Children:
Children = DISTINCT ( data[Child] )
2. Set up a relationship with the main table.
3. Create a measure:
Flag =
VAR Count_of_parents = COUNT ( data[Parent] )
VAR Total_count = CALCULATE ( DISTINCTCOUNT ( data[Parent] ), REMOVEFILTERS ( 'Children' ) )
RETURN Total_count - Count_of_parents
4. Add a table visual with the field 'Children'[Child] and the measure [Flag]
5. Set a filter in the visual "[Flag] = 0"
6. (optional) Add a rectangle to hide the [Flag] column.
Here's the output (w/o the rectangle):
If somebody proposes a cuter solution, I'll be happy to upgrade my knowledge. 🙂
Thank you so much barritown for the solution. This has helped a lot. For any further Queries I will directly connect with u.
Hi Rahul,
Sounds like you could filter the child IDs where they count >1 something like this:
Hi John Thanks for solution . But I need to display common record in table column , not just count. can u pls suggest solution.
Hi Rahul, this expression returns a scalar value you can use to filter your visual:
just remove the ">1" part of the measure to turn it into a count then apply a visual filter where IsCommon >1 instead:
Thank you so much John for the solution. In case of any further Query I will directly connect with u.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |