DAX for filtering based on Selected Value

[Anonymous]

Hello,

I'm trying to get a subset of the below table based on the user selected filter

    +------------+---------+----------------+------------+------------+
    | Fiscal Qtr | Period  | Product Family | DataPoint1 | DataPoint2 |
    +------------+---------+----------------+------------+------------+
    | FY2020Q1   | Actuals | 11-RAF         |       325  |  11        |
    | FY2020Q1   | Commit  | 11-RAF         |       322  |  (22)      |
    | FY2020Q1   | Week 5  | 11-RAF         |       312  |  (21)      |
    | FY2020Q1   | Week 9  | 11-RAF         |       319  |  (10)      |
    | FY2020Q2   | Actuals | 11-RAF         |       248  |  (35)      |
    | FY2020Q2   | Commit  | 11-RAF         |       271  |  (30)      |
    | FY2020Q2   | Week 5  | 11-RAF         |       282  |  (30)      |
    | FY2020Q2   | Week 9  | 11-RAF         |       252  |  (27)      |
    | FY2020Q3   | Actuals | 11-RAF         |       250  |  (14)      |
    | FY2020Q3   | Commit  | 11-RAF         |       321  |  (30)      |
    | FY2020Q3   | Week 5  | 11-RAF         |       325  |  (30)      |
    | FY2020Q4   | Actuals | 11-RAF         |       295  |  (34)      |
    | FY2020Q4   | Commit  | 11-RAF         |       332  |  (30)      |
    | FY2020Q4   | Week 5  | 11-RAF         |       327  |  (32)      |
    | FY2020Q4   | Week 9  | 11-RAF         |       320  |  (37)      |
    | FY2021Q1   | Actuals | 11-RAF         |       200  |  (4)       |
    | FY2021Q1   | Commit  | 11-RAF         |       265  |  (39)      |
    | FY2021Q1   | Week 5  | 11-RAF         |       265  |  (37)      |
    | FY2021Q1   | Week 9  | 11-RAF         |       193  |  (19)      |
    | FY2021Q2   | Actuals | 11-RAF         |       198  |  (34)      |
    | FY2021Q2   | Commit  | 11-RAF         |       204  |  (27)      |
    | FY2021Q2   | Week 5  | 11-RAF         |       225  |  (36)      |
    | FY2021Q2   | Week 9  | 11-RAF         |       198  |  (41)      |
    | FY2021Q3   | Actuals | 11-RAF         |       198  |  (7)       |
    | FY2021Q3   | Commit  | 11-RAF         |       199  |  (17)      |
    | FY2021Q3   | Week 5  | 11-RAF         |       284  |  (19)      |
    | FY2021Q3   | Week 9  | 11-RAF         |       255  |  2         |
    | FY2021Q3   | Week 12 | 11-RAF         |       257  |  1         |
    +------------+---------+----------------+------------+------------+

For example: If the user chooses a slicer with Quarter=FY2021Q3 and Period = Week12 the result should show Period'"Actuals" for all other Fiscal Quarters.

The end result:

+------------+---------+----------------+---------------+
| Fiscal Qtr | Period  | Product Family | DataPoint2DAX |
+------------+---------+----------------+---------------+
| FY2020Q1   | Actuals | 11-RAF         |  11           |
| FY2020Q2   | Actuals | 11-RAF         |  (35)         |
| FY2020Q3   | Actuals | 11-RAF         |  (14)         |
| FY2020Q4   | Actuals | 11-RAF         |  (34)         |
| FY2021Q1   | Actuals | 11-RAF         |  (4)          |
| FY2021Q2   | Actuals | 11-RAF         |  (34)         |
| FY2021Q3   | Week 12 | 11-RAF         |  1            |
+------------+---------+----------------+---------------+

Here's what I'm trying:

DataPoint2 DAX = CALCULATE(AIO[DataPoint2],AIO[Fiscal Qtr]="FY2021Q3",AIO[Period]="Week 12")

This gets me the value for FY2021Q3 only but how do I expand this to get all the other Fiscal Quarters with Period="Actuals"?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You need to extract each period and the fiscal qtr field as a single calculated table and use it as the slicer:

Fiscal table = DISTINCT('AIO'[Fiscal Qtr])
Period table = DISTINCT('AIO'[Period])

Create a measure like this, put it in the table visual filter and set its value as 1:

Visual control = 
IF (
    (
        SELECTEDVALUE ( 'AIO'[Fiscal Qtr] )
            = SELECTEDVALUE ( 'Fiscal table'[Fiscal Qtr] )
            && SELECTEDVALUE ( AIO[Period] ) = SELECTEDVALUE ( 'Period table'[Period] )
    )
        || (
            SELECTEDVALUE ( 'AIO'[Fiscal Qtr] )
                <> SELECTEDVALUE ( 'Fiscal table'[Fiscal Qtr] )
                && SELECTEDVALUE ( AIO[Period] ) = "Actuals"
        ),
    1
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yingjlThanks for your response. Do you know why I can't recreate the same view using a "Matrix" visual instead of a "Table" visual? I get blanks when I put Product Family on ROWS and Fiscal Qtr on COLUMNS

[selimovd]

Hey @Anonymous ,

why don't you just use slicer?

You can add a slicer for Fiscal Qtr and one for Period. The table will the filter automatic to your selection.

Check out the documentation:

Slicers in Power BI - Power BI | Microsoft Docs

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd- The slicer will filter all the data points but I want to get the Period="Actuals" for all the quarters that are not in the user selection.