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

Hi mcairney

 

are you able to post a sample dataset ?

  • mcairney's avatar
    mcairney
    New Member
    7 years ago

     Here is a basic sample of what I'm trying to do   

    • 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