Forum Discussion

BoNDoK's avatar
BoNDoK
Frequent Visitor
6 years ago
Solved

Use a variable in filter as operand

I have the following tables in Power BI:  FactData Filtering Table I need to create another table programmatically (using DAX) to filter the Fact Table using the selected item from the...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi BoNDoK 

     

    Let me know if you'd like to get below results:

    1. DONOT manage the relationship between them

    2. Add a measure and drag it to the filter pane in FactData table visual, set it as 1

    Measure = IF(SELECTEDVALUE('Filtering Table'[Col1])=MAX(FactData[Cola]),1)

     

    3. final results:

    Pbix attached.

BoNDoK's avatar
BoNDoK
Frequent Visitor
6 years ago

Hi Nathaniel,

 

Thank you for your response.

 

I cannot add this relationship due to other needed relationships in my model.  I have a requirement from a user, to show custom legends on a line chart.  And to achieve it, I need to be able to establish the connection I described without using Power BI relationships.

 

Regards,

BoNDoK

parry2k's avatar
parry2k
Super User
6 years ago

BoNDoK you cannot create a calculate table using slicers. if you explain what you are trying to achieve and why you need this calculated table

  • BoNDoK's avatar
    BoNDoK
    Frequent Visitor
    6 years ago

    Hi Parry,

     

    Here is what I need to achieve:

    I have a sales table, with a hierarchy (region, country, product category, product subcategory) and date dimension.  The user would like to see the numbers on a line chart, with the hierarchy as a slicer, and date on x-axis and the trick is: when any item of the slicer is selected: the chart legend should show the children of the node selected, and if nothing is selected it should show the first level nodes (regions).

     

    My approach is:

    1. Transpose the fact table so each of the 4 levels is on a separate line. 

    2. Use the transposed table as the legend.  But extra filtering is needed and that's why I would like to filter based on the selected member of a slicer.

     

    Thank you,
    BoNDoK