Forum Discussion

ddimitrov's avatar
ddimitrov
Regular Visitor
3 years ago
Solved

How to select data from another table with a single slicer

Hello PowerBI ninjas, gurus, delta forcers.    I am a newcomer to PowerBI and need to design a PowerBI report for our project team.  I tried several approaches to resolve an issue with a slicer fo...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi ddimitrov ,

    According to your description, here's my solution. Create two measures.

    MdaysTarget =
IF (
    ISFILTERED ( Actuals[Program] ),
    SUMX (
        FILTER (
            ALL ( 'Targets' ),
            'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
                && 'Targets'[Program] = SELECTEDVALUE ( 'Actuals'[Program] )
        ),
        'Targets'[Mdays]
    ),
    IF (
        ISFILTERED ( 'Actuals'[Quarter] ),
        SUMX (
            FILTER (
                ALL ( 'Targets' ),
                'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
            ),
            'Targets'[Mdays]
        ),
        SUM ( 'Targets'[Mdays] )
    )
)

    Difference =
'Actuals'[MdaysTarget] - SUM ( 'Actuals'[ Mdays] )

    Get the result.

    I attach the file below for your reference.

     

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

    Best regards,

    Community Support Team_yanjiang

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi ddimitrov ,

According to your description, here's my solution. Create two measures.

MdaysTarget =
IF (
    ISFILTERED ( Actuals[Program] ),
    SUMX (
        FILTER (
            ALL ( 'Targets' ),
            'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
                && 'Targets'[Program] = SELECTEDVALUE ( 'Actuals'[Program] )
        ),
        'Targets'[Mdays]
    ),
    IF (
        ISFILTERED ( 'Actuals'[Quarter] ),
        SUMX (
            FILTER (
                ALL ( 'Targets' ),
                'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
            ),
            'Targets'[Mdays]
        ),
        SUM ( 'Targets'[Mdays] )
    )
)

Difference =
'Actuals'[MdaysTarget] - SUM ( 'Actuals'[ Mdays] )

Get the result.

I attach the file below for your reference.

 

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

Best regards,

Community Support Team_yanjiang

  • ddimitrov's avatar
    ddimitrov
    Regular Visitor
    3 years ago

    v-yanjiang-msft 

    I tested it and it worked. 

    Your solution is more elengant than mine. Congratulations 🙂 

    Thanks a lot. 

  • ddimitrov's avatar
    ddimitrov
    Regular Visitor
    3 years ago

    Thanks v-pn 

     

    I will test it now. 

     

    Just for the record, last night I did a final tweak and it worked for my report: 

     

    Mdays-Ms= IF(
    ISINSCOPE('actuals'[Quarter]),
    SUMX(
        FILTER(
            targets,
            'targets'[Quarter] = SELECTEDVALUE('actuals'[Quarter])),
        'targets'[Mdays]),
    BLANK()
    )

     

    the only change is replaced the hardcoded quarter value with: 

    SELECTEDVALUE('actuals'[Quarter])