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.
I have been stuck on this modelling problem for last couple of days, I have my model currently setup like this,
I want to filter SKU forecast table with the column Status from INOUTstatuses table. Both are connected to date table and item table with 1 to * relation. An excerpt of inout table looks liek this,
Each sku has a status with a respective date. What I want to do is, add status column in the filter pane and selected a status for a respective year and it should show all the sku under those two conditions and measures and columns calcuated in forecast table should dynamically change. I cannot use bi-directional filter too in this case. Any help is much appreciated.
See if this works. Firts, the sample model
To filter Table 2 based on the selection in the filter from the Status table, create the follwoing measure:
Filter Table 2 =
IF (
ISFILTERED ( 'Status Table'[Status] ),
COUNTROWS (
SUMMARIZE ( 'Table 1', 'Table 1'[Status], 'Dim Sku Code'[dSKU code] )
),
1
)
In the visual for Table 2, add the SKU field from the dimension table to the filter pane, select TopN as the filter, add the [Filter Table 2] measure and set the topn to 1:
to get
Sample PBIX file attached
Proud to be a Super User!
Paul on Linkedin.
Thanks, This look promising, however I've already looked into creating dynamic measure as a filter option, but the problem I have multiple pages and around 20 different visuals, so I need this status filter as a page level or report level one. Is it possible to modify this solution? I have pondered over it, but couldn't seem to think of a way a calculated column that is dynamic!
I cannot think of a way to make this workable at page/report level. If the solution needs to be dynamic, you are going to need to use a measure, and measures cannot be applied at page/report level filters
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |