Forum Discussion

mcairney's avatar
mcairney
New Member
7 years ago

Create a Calculated Table using a UNION that preserves all values in one table

I have two tables withe the same structure.   In one I have rows that I always want to include.  In the other I have a slicer based on this which the user can select the rows they are interested in...
Need Help
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Hello mcairney

 

thanks for the file. You need to create a calculate table with this DAX Query:

 

You can download the file: https://1drv.ms/u/s!AiiWkkwHZChHj0R4TRtHxsgRDnct

 

UnionTable = 
UNION(
    ADDCOLUMNS( ALLNOBLANKROW('Reference Site'), "SourceTable", "ReferenceSite" ),
    ADDCOLUMNS( ALLNOBLANKROW('User Site'), "SourceTable", "UserSite" )
)

Do not relate it to any table. In the slicer, use the Key from the 'Site Metrics' table. Then add a table visual and add this measure as a visual filter = 1

 

Measure = 
CALCULATE(
    COUNTROWS( UnionTable ),
    TREATAS(
        UNION(
            CROSSJOIN( ALL( 'Site Metrics'[Site Key] ), {"ReferenceSite"} ),
            CROSSJOIN( VALUES( 'Site Metrics'[Site Key] ), {"UserSite"} )
        ),
        UnionTable[Site Key], UnionTable[SourceTable]
    )
)
mcairney's avatar
mcairney
New Member
7 years ago

LivioLanzo I worked out where my version was going wrong.

 

However, now I need to look at extending this a bit further.   If I have more than 1 Metrics table, does this solution extend out to be able to appply the same filter across that?

 

Thanks

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Hi mcairney

     

    it should be possible. What are you trying to do exactly?

    • mcairney's avatar
      mcairney
      New Member
      7 years ago

      LivioLanzo  Assume that there is 2 additional tabl;es with a similar structure to Site Metrics  - lets call them Site Metrics2 and Site Metrics 3

       

      Again they have relationships to Site Key and Region.

       

      I want this slicer to also filter the values in those other tables.   I can mock this up if you need?

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi mcairney,

         

        For your requirement, I have made a test. You could create the relationship like below.

         

         

        Then the slicer of  Site Key and Region will filter the table Site Metrics2 and Site Metrics 3.

         

         

        Best Regards,

        Cherry