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