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

  • ddimitrov's avatar
    ddimitrov
    Regular Visitor
    3 years ago

    Thanks for the advice, Amit. 

    I will test it in coming days and will share feedback. 

  • ddimitrov's avatar
    ddimitrov
    Regular Visitor
    3 years ago

    amitchandak 

     

    In this particular issue, with the described setup above, I am trying to resolve the following: 

    • for Slicer-Quarter -- I use: actuals[Quarter]
    • in table: Actuals -- I created a measure: actuals[Mdays-Ms]
    • in Matrix: Targets vs Actuals -- I added measure: targets[Mdays-Ms]
    • this is the logic/code I use for the measure: 
     

     

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

     

    However, this logic works only for hardcoded value for quarter. I would like the hardcoded: "Q2 23" to be the Slicer-Quarter selection.

     

    What is the proper logic/code I use for measure: actuals[Mdays-Ms] so it gets:

    • for Quarter selected -- exact values for programs from column: targets[Mdays]
    • for Program selected -- exact values for Quarters from column: targets[Mdays]
    • for Quarter and Program selected -- exact value for the program and quarter from column: targets[Mdays]
    • for nothing selected -- exact values for each program and each quarter