Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Dimension Filter

Hi All,   Is there any alternate way to achieve this below blog? https://community.powerbi.com/t5/Desktop/Column-Selection-using-a-Disconnected-table/m-p/820490#M394243 I don't want to duplicate ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    How about create a calculated table and create a measure as a visual level filter?

    Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    )
)

Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

    Result would be shown as below.

     

    Pbix as attached.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

1. The purpose of the Measure here is to determine whether the period in the table is equal to the selected value from slicer and the purpose of giving the Measure in Visual Level filter is to filter blank value.

2. There's no need to associate Calculated table and (Main)Table.

3. Actually, the Measure is fine and it was a little mistake of mine that I didn't mention that i have created another slicer table.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

Cant we create Weekday same as  Month,Year and quarter in the below formula?

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    )
)

Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

Thanks

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Yes, of course we can. Modify the formula as below and we can get a table with weekday.

    Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", FORMAT('Table'[date],"DDDD"),
        "value", 'Table'[value],
        "period", "Weekday"
    )
)

    Or we can use WEEKDAY() funtion instead of FORMAT() function to get 1-7 as monday to sunday.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Sorry my bad. Typo mistake. 

       

      Cant we create Week Number in the below formula?

       

      i.e., 1-52 for a calendar year. I would like to see weekly data.

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        Please use WEEKNUM function() instead.

        Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", WEEKNUM('Table'[date]),
        "value", 'Table'[value],
        "period", "Weeknum"
    )
)

        Result would be shown as below.

         

        Best Regards,

        Jay

        Community Support Team _ Jay Wang

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