Forum Discussion

wvadik's avatar
wvadik
Icon for Helper III rankHelper III
5 years ago

One slicer for two fileds

Hi. I have table facts (FactTable) with 3 columns (Current Country, Previous Country, Quantity) and one dimension table (catalogue_Country). On report form I use calculate table (CalcTable:=DATATAB...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi wvadik ,

I update your sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column Country ID

 

Country ID = 
IF (
    'FactTable'[Current Country] = 0
        && 'FactTable'[Previous Country] <> 0,
    'FactTable'[Previous Country],
    'FactTable'[Current Country]
)

 

2. Create relationship between FactTable(Field: Country ID) and catalogue_Country(Field: id)

Best Regards

wvadik's avatar
wvadik
Icon for Helper III rankHelper III
5 years ago

Anonymous, thanks.

How about switching by slicer "Include Previous Country"?

when it's "No"

 

when it's "Yes"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi wvadik ,

    It may need to create a measure as below instead of calculated column to achieve it. But the problem is that the quantity can't be aggregated and the data can't be filtered by the country name.... You can find the details in Page 2 of the attachment.

    Measure = 
VAR _tab =
    ADDCOLUMNS (
        'FactTable',
        "NewCountry",
            IF (
                SELECTEDVALUE ( 'CalcTable'[Include Prev Country] ) = "No",
                MAX ( 'FactTable'[Current Country] ),
                IF (
                    MAX ( 'FactTable'[Current Country] ) = 0
                        && MAX ( 'FactTable'[Previous Country] ) <> 0,
                    MAX ( 'FactTable'[Previous Country] ),
                    MAX ( 'FactTable'[Current Country] )
                )
            )
    )
RETURN
    MAXX (
        FILTER (
             ALL('catalogue_Country') ,
            'catalogue_Country'[id] = MAXX ( _tab, [NewCountry] )
        ),
        'catalogue_Country'[name]
    )

    Best Regards