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 ,

 

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous  Thanks for your help!!