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 the records or dont want to go for a discoonect table.

 

Thanks in Advance.

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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 ,

       

      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.

      • 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.