Forum Discussion

TanHY's avatar
TanHY
Helper I
2 years ago
Solved

Matrix Table

Hi Guys, I create a matrix table as below : 

  • This matrix table involves 2 tables which the relationship is 1 to many ( cross filter enable- as i want my fact filter my dim table also). 
  • The column is from the dim table (one) , while the row and values are from the fact table.

As you can see from the table, the table is filled with status (open, closed , etc...). I would like to create a if else condition where those blank show "pending" else "submitted".

Hope can get some response from you all. Thank you.

 

 

Need Help
  • Sergii24's avatar
    Sergii24
    2 years ago

    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! 

     

5 Replies

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    Hi TanHY, you can ahieve it by creating a necessary calculated column with your IF statement and then drag and drop it to the matrix instead of Status 😉 

  • TanHY's avatar
    TanHY
    Helper I
    2 years ago

    Hi Sergii24 ,

    Thanks for your prompt reply. May I know how should I write the IF statement? I am thinking of that too.., but the outcomes seems similar like the table i shown. I also thinking of using cross filtering, but worry about the sizes of my data model.

    Hope can get your reply. Thank you.

    • Sergii24's avatar
      Sergii24
      Super User
      2 years ago

      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:

       



      • TanHY's avatar
        TanHY
        Helper I
        2 years ago

        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