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.
Hello All,
I have a slicer which has 4 rows these as follows:
Slicer:
1) This month
2)Last Year
3) This year
4) Prev month
and in another table I have columns like this
Country, State, This month, Last Year, This year, Prev month
So i have modeled the data like
(first table )This month--> This month (Second Table)
and Last Year -->Last Year and
This year -->This year and
Prev month--> Prev month
Since a table can have only one active relationship This month--> This month is active.
Can I make Active relation based on the slicer
For Example: If I select "Prior Year" all data must be filtered to prior year ...etc
Thanks
Solved! Go to Solution.
@Anonymous you need to unpivot table b and then set the relationship between both the table, after that it will work as expected.
To unpivot, select all column table b excep (those four columns) and right click after selection and clicking unpivot other column, it will give you two column attribute and value (change column name as per your need)
Now set relationship between table 2 with table 1 on attribute and selection.
Now if you filter from selection, you table2 will show respective values.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous unfortunaltey you cannot change active relationship on selection but in your dax measure you can use userrelationship function to make other function active based on selection.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Table 1
Id | Selection |
1 | This month |
1 | This year |
2 | Prev MOnth |
2 | Last Year |
Table 2
State | Country | Id | This month | This Year | Prev Month | Last Year | Customer | DOB |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
I modeled 2 tables as follows:
Table 1 "Selection colum " --> This month
"Selection colum " --> This year (Active relation ship)
"Selection colum " --> Prev month (Non-Active relation ship)
"Selection colum " --> Last year ( non- Active relation ship)
Since a table has only 1 active relation, "Selection column " --> This month is active
now in report, I am using Table 1 "Selection" as a slicer,
and it will display as
This month |
This year |
Prev Month |
Last Year |
If I select "this month" in table 1 it should map to "this month" column in table 2 and data must be filtered
if I select "This year " in table 1 it should map to "This year " column in table 2 and data must be filtered
...
Hope this is clear.
Thanks
Aditya
@Anonymous you need to unpivot table b and then set the relationship between both the table, after that it will work as expected.
To unpivot, select all column table b excep (those four columns) and right click after selection and clicking unpivot other column, it will give you two column attribute and value (change column name as per your need)
Now set relationship between table 2 with table 1 on attribute and selection.
Now if you filter from selection, you table2 will show respective values.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
My data is very huge, Cannot unpivot, is there any other way.?
@Anonymous i think yes it can be done with other method, I have to make a solution, hard to explain if you can share sample data in excel thru one drive/google drive, i will try to get back to you. cheers!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Can I create user relationship like a custom column instead of measure, because If I use to measure data is changing as My selection criteria by remaining slicers' data is not getting filtered .
Any Alternate approach to a situation like this?
@Anonymous userrelationship can be only used in measure. I exactly not sure whst you are trying to achieve, if you share sample data and what result you are looking for, it will help to see if there is alternate solution.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |