Date Dimension Filter

[Anonymous]

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#M394...

I don't want to duplicate the records or dont want to go for a discoonect table.

Thanks in Advance.

[v-jayw-msft · Accepted solution]

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]

Hi @v-jayw-msft ,

I think it is working fine.

But i have few quetions

1. What is the purpose of giving the Measure in Visual Level filter? I coundn't able to understand the reason behind it.

2. Do we need to associate this Calculated table and (Main)Table in the data model? or just let it be two island table in power bi data model.

3. Also i have doubt in the below measure whether it is period[period] or 'Table 2'[period]? I think it is Table 2'[period].

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

Thanks in Advance

Note:Unfortunately i couldn't able to open the PBIX file because of issues from my side. That is the reason i am asking these queries.

[v-jayw-msft]

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]

Hi @v-jayw-msft ,

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

[v-jayw-msft]

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]

Hi @v-jayw-msft ,

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

[v-jayw-msft]

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]

Hi @v-jayw-msft  Thanks for your help!!

[Anonymous]

Hi @Anonymous ,

I usually use a calculated table from the dates existing in the fact table like:

TABLE = calendar(MIN('Fact'{Date],MAX('Fact'[Date])

Let me know if it worked, if so mark as solution.

Best Regards,

Duarte Raminhos

[Anonymous]

Hi @Anonymous ,

https://powerpivotpro.com/2018/01/dynamic-attributes-power-bi-report/

Actually in this blog to create Dynamic attributes in a slicer we are going for disconnect table and by performing Unpivot function the records are getting duplicate by 4 times (if we require 4 values in a slicer). Can we avoid this duplicancy of records and is there any alternate way of achieving Dynamic Attributesin Power BI.

Regards,

Prakash