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.

 

I want to then have a table which is the UNION of the two tables.

 

I have tried using :   CalcTable = UNION ( 'Filtered Tabled', ALL( 'Static Table))

 

I then put a Table on the report page that displays the values from the CalcTable.   However it does not seem to include the static rows and only shows me the filtered rows.

 

What am I doing wrong here?

Need Help

10 Replies

      • 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]
    )
)
  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi mcairney,

     

    From your pbix, it seems that you have created the relationship like below. So when you  select the slicer based on Site Key from User Site table, it will show the filtered value.

     

     

    I'm a little confused about your requirement, do you only want to get the all rows like below which won't be filtered  by the selection of the slicer.

     

    If it is, you could delete the relationship between the table User Site and All Sites, but that slicer seems to be meaningless if you remove the relationship.

     

    Best Regards,

    Cherry

    • mcairney's avatar
      mcairney
      New Member
      7 years ago

      Hi Cherry

       

      The relationships were where I was also trying different options to find what works.

       

      What I need is to have 2 sets of Sites and then combine them.  However only the rows from the "User Site" table can be filtered and the ones in the "Reference Site" table are all included.   The combined set should then filter the "Site Metrics" table and the final data set then used on the chart to show the "Reference Site" differently from the "User Site".

       

      So as I select e.g. the value "Kof18" in the slicer, the final table should have the values "Par15", "Feb22" and "Kof18"