Forum Discussion

mriveraa's avatar
mriveraa
Regular Visitor
3 years ago
Solved

Change table based on slicer alternative

Hello Everyone,

 

Supposed I have the following data: 

Now I want to compute the difference of the module.name and module.version based on a selection of two versions and show it to another table.  

 

For Example

If I have selected version 100 and 102 

Then I expect to get a table showing their difference 

Now I tried to have a calculate table with the Dax Expression

Difference = EXCEPT(
SELECTCOLUMNS(CALCULATETABLE(Module,FILTER(Module,Module[Document.version]=[SlicerTable2])),
"name",Module[Document.modules.name],"version",Module[Document.modules.version]),
 
SELECTCOLUMNS(CALCULATETABLE(Module,FILTER(Module,Module[Document.version]=[SlicerTable1])),"name",Module[Document.modules.name],"version",Module[Document.modules.version]))
 
But it didn't work because from what I saw in the forums calculated columns or tables are not dynamic and cannot be changed to slicer values during runtime. 
 
Is there an alternative way to do this to have the difference be shown during runtime selection? 
 
Thanks in advanced,
Mark

 

 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi mriveraa ,

     

    As far as I know, Power BI doesn't support calculated table/column be dynamic based on slicer or filter. I suggest you to create measures to achieve your goal.

    Data model:

    SlcierTable1 = VALUES('Module'[Document.version])
    SlcierTable2 = VALUES('Module'[Document.version])
    DimModuleName = VALUES(Module[Document.modules.name])

    Measure:

    Difference = 
VAR _SELECT1 =
    SELECTEDVALUE ( SlcierTable1[Document.version] )
VAR _SELECT2 =
    SELECTEDVALUE ( SlcierTable2[Document.version] )
VAR _VIRTUAL_TABLE =
    EXCEPT (
        SELECTCOLUMNS (
            CALCULATETABLE (
                Module,
                FILTER ( Module, Module[Document.version] = _SELECT2 )
            ),
            "name", Module[Document.modules.name],
            "version", Module[Document.modules.version]
        ),
        SELECTCOLUMNS (
            CALCULATETABLE (
                Module,
                FILTER ( Module, Module[Document.version] = _SELECT1 )
            ),
            "name", Module[Document.modules.name],
            "version", Module[Document.modules.version]
        )
    )
RETURN
    MAXX (
        FILTER ( _VIRTUAL_TABLE, [name] = MAX ( DimModuleName[Document.modules.name] ) ),
        [version]
    )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mriveraa ,

     

    As far as I know, Power BI doesn't support calculated table/column be dynamic based on slicer or filter. I suggest you to create measures to achieve your goal.

    Data model:

    SlcierTable1 = VALUES('Module'[Document.version])
    SlcierTable2 = VALUES('Module'[Document.version])
    DimModuleName = VALUES(Module[Document.modules.name])

    Measure:

    Difference = 
VAR _SELECT1 =
    SELECTEDVALUE ( SlcierTable1[Document.version] )
VAR _SELECT2 =
    SELECTEDVALUE ( SlcierTable2[Document.version] )
VAR _VIRTUAL_TABLE =
    EXCEPT (
        SELECTCOLUMNS (
            CALCULATETABLE (
                Module,
                FILTER ( Module, Module[Document.version] = _SELECT2 )
            ),
            "name", Module[Document.modules.name],
            "version", Module[Document.modules.version]
        ),
        SELECTCOLUMNS (
            CALCULATETABLE (
                Module,
                FILTER ( Module, Module[Document.version] = _SELECT1 )
            ),
            "name", Module[Document.modules.name],
            "version", Module[Document.modules.version]
        )
    )
RETURN
    MAXX (
        FILTER ( _VIRTUAL_TABLE, [name] = MAX ( DimModuleName[Document.modules.name] ) ),
        [version]
    )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.