Forum Discussion
Matrix Table
Hi TanHY, there are multiple ways to handle values present in dimensional table, which are not available in fact table (e.g. W11):
- First of all you need to asnwer a question: do you need those values for any calculation in your report?
- If not, you can remove them in PowerQuery or even in the data soruce: you can use merge between fact and dimensional tables and filter out unexisting values from dimesnional table. This apporach might not be the most efficient in PowerQuery but that's one way to do it.
- If you need them/don't want to complicate PowerQuery, then:
- use page/report level filter FactTab le[Key] <> blank, this should remove unmatched keys from all visuals used at the page/report
- you can keep using DAX to control visual aspect of such values, so they remain in the visual but, for instance, you replace "pending" with, let's say, blank() for cases when key from Dimensional Table is not present in the Fact table
Good luck with developing your report!
- First of all you need to asnwer a question: do you need those values for any calculation in your report?
Hi TanHY, actually calculated column won't solve the problem: it can replace values "Open" and "Close" with something else, such as "Submitted", but nothing will happen to unexisitng combinations (such as W00 - CLB ).
We'll need a measure to work with those cell. The measure we're going to create will search for values in fact tables, but when result is blank, it will be replaced with "Pending" text.
New Status =
VAR _CurrentStatus = SELECTEDVALUE( 'Fact Table'[Status], "Pending" ) //get the value of status for matrix intersection. If not present (i.e. not existing rows in fact table), then write "Pending"
RETURN
IF(
_CurrentStatus <> "Pending", //if variable is not pending, it means we've managed to get a value "open" or "closed", so we simply replace output with "Submitted"
"Submitted",
_CurrentStatus
)
And here is the comparsion of outputs:
The model and tables used:
Dimensional Table:
Fact table:
Hi Sergii24 ,
Your solution is helpful! Thanks. One more challenge part is, the key (as per your data model) might have some data is not related to the Fact Table, eg
[Key]:
W001, W002, W11
"Fact Table"[Key]:
W001, W002
In this case, the measure you provided will make the matrix pop out another column which is W11, with all "Pending".
Can this be solve ? Or I should clean the data out , in this case, I will just left join fact table with the [Key] table.
Hope can get your reply. Thanks
Hi TanHY, there are multiple ways to handle values present in dimensional table, which are not available in fact table (e.g. W11):
- First of all you need to asnwer a question: do you need those values for any calculation in your report?
- If not, you can remove them in PowerQuery or even in the data soruce: you can use merge between fact and dimensional tables and filter out unexisting values from dimesnional table. This apporach might not be the most efficient in PowerQuery but that's one way to do it.
- If you need them/don't want to complicate PowerQuery, then:
- use page/report level filter FactTab le[Key] <> blank, this should remove unmatched keys from all visuals used at the page/report
- you can keep using DAX to control visual aspect of such values, so they remain in the visual but, for instance, you replace "pending" with, let's say, blank() for cases when key from Dimensional Table is not present in the Fact table
Good luck with developing your report!
- First of all you need to asnwer a question: do you need those values for any calculation in your report?