locked
Dynamic filter in a resume flag table RRS feed

  • Question

  • I have this scenario in a Tabular Cube (DAX) published in Azure Analysis Service.

    I have the Customer and Date dimension related to two factual tables (Sales Store and Sales Online ). In addition to these two factual dimensions I still have a Location Dimension related only to the factual table Sales Store and the Dimension Payment Method to the factual Sales On-Line.

    Examples fact tables:

    Fact Sales Store

    Client_ID    Data_FK         Location_Store     ...     Sales_Store_ID

    2                  20181001       City A                     ...       1

    3                  20181001       City B                     ...       2

    4                  20181003       City A                     ...       3

    7                  20181003       City B                     ...       4

    9                  20181004       City B                     ...       5

    10                20181005       City A                     ...       6

    15                20181005       City A                     ...       7


    Fact Sales On-Line

    Client_ID      Data_FK         ...    Sales_OnLine_ID

    1                  20181001         ...    1

    3                  20181002         ...    2

    5                  20181003         ...    3

    6                  20181003         ...    4

    9                  20181004         ...    5

    10                20181005         ...    6

    13                20181006         ...    7


    In Power BI Desktop create a table with the Client ID, Sales_S_ID and Sales_OnL_ID:

    Client_ID  Sales_Store_ID     Sales_OnLine_ID

    1 1

    2 1

    3 1 1

    4 1

    5 1

    6 1

    7 1

    9 1 1

    10 1

    13 1

    15 1

    So far so good, but I tried to create a Venn diagram.

    I did not make it. To be able to use this visual (Venn Diagram) data must be in this format.

    Client_ID     Sales_Store_ID    Sales_OnLine_ID

    1                    0                           1

    2                    1                           0

    3                    1                           1

    4                    1                           0

    5                    0                           1

    6                    0                           1

    7                    1                           0

    9                    1                           1

    10                  1                           0

    13                  0                           1

    15                  1                           0

    Any suggestion?

     
    I created in the Dim Client two columns with a Sales Store and Sales On-Line flag. 
    I filled out the columns with the following Formula:

    Sales Store = IF(LOOKUPVALUE(‘Fact Sales Store’[ID_Client]; ‘Fact Sales Store’[ID_Client];’Dim Client’[ID])<>0;1;0)

    DIM_Client

    ID

    Name

    Sales Store

    Sales On-line

    1

    0

    1

    2

    1

    0

    3

    1

    1

    4

    1

    0

    5

    0

    1

    6

    0

    1

    7

    1

    0

    8

    0

    0

    9

    1

    1

    10

    1

    0

    11

    0

    0

    12

    0

    0

    13

    0

    1

    14

    0

    0

    15

    0

    1

    The result is not expected, ie as an example when I apply a filter in the DIM Location linked only to the Sales Store I was expecting it to be reflected in the Sales Client column of the Dim Client, it does not happen, the Dim Client is always unchanged. It needed to be dynamic by changing the value of the flag depending on the fact of the fact.
     

    Example: Filter the location for City B, so sales to customers in that city would have the flag repopulated,

    and Dim Client should look like this.

    The visual I am to use (Venn diagram) only allows to use columns,

    does not allow the use of metrics.

    ID

    Sales Store

    Sales On-line

    1

    0

    1

    2

    1

    0

    3

    0

    1

    4

    1

    0

    5

    0

    1

    6

    0

    1

    7

    0

    0

    8

    0

    0

    9

    0

    1

    10

    1

    0

    11

    0

    0

    12

    0

    0

    13

    0

    1

    14

    0

    0

    15

    0

    1


    Any suggestion? Any alternative? 

    I also tried to create a table calculated with a Summarize of the Customer Dimension,

    and the Facts tables, but it did not work either.

    It happened the same, the lines are initially calculated and are not recalculated / repopulated

    after applying the filters in the dimensions that relate to the factual.

    Tanks.






    Monday, January 14, 2019 4:00 PM

Answers

  • So the only way to get the data to behave in the way you are describing is to use measures.

    But if the Venn diagram custom visual you want to use only supports using columns then you are stuck and I think you will just have to look for a different way of presenting this data.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Joaquim Lapa Friday, January 18, 2019 11:53 AM
    Tuesday, January 15, 2019 10:19 PM

All replies

  • ...It happened the same, the lines are initially calculated and are not recalculated / repopulated after applying the filters in the dimensions that relate to the factual.

    This is how calculated tables and columns are designed to work. They are only evaluated once when the data is loaded. They are not re-evaluated in the context of a given query.

    If you want metrics to calculate at runtime you need to use a measure.

    One simple fix in your situation might be to just add 0 to the rowcount.

    eg.

    Online Sales Count:=COUNTROWS('Online Sales') + 0

    This should force a 0 instead of a blank value when there are no rows.


    http://darren.gosbell.com - please mark correct answers

    Monday, January 14, 2019 9:42 PM
  • Hi  Joaquim Lapa,

    I test your expression in my environment, it will work

    Use below expression to create calculated column 

    CALONLINE = IF(LOOKUPVALUE('Online sale'[Client_ID],'Online sale'[Client_ID],'Table 0'[ID])<>0,1,0)
    
    CALSALE = IF(LOOKUPVALUE('fact_sale store'[Client_id], 'fact_sale store'[Client_id],'Table 0'[ID])<>0,1,0)


    It will get  the result like below

     So I don't know which part in your design don't work. If possible, could you please inform me  in details.

    In addition, you also could refer to Darren's suggestions to see whether it works or not.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 15, 2019 2:45 AM
  • Thank you for the answer.
    The concept that has been advanced is a filter concept on the flags that is not what I want, when filtering City B, the count in the CALSALE column becomes three and that's ok, but the CALONLINE column should be unchanged (in 7). the Filter City B applied only in a factual leading to the recalculation/repopulated of the CALSALE column the squares in red should be changed to "0".
    I don't know if it's possible? but ...

    The result I want is the following (in red squares, the value zero should appear)


    Tuesday, January 15, 2019 11:42 AM
  • So the only way to get the data to behave in the way you are describing is to use measures.

    But if the Venn diagram custom visual you want to use only supports using columns then you are stuck and I think you will just have to look for a different way of presenting this data.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Joaquim Lapa Friday, January 18, 2019 11:53 AM
    Tuesday, January 15, 2019 10:19 PM
  • Thanks for the reply.
    In the model I only put two areas of analysis, but in truth in my model I have more, and the
    Venn Diagram is very flexible, I add a new area of analysis and the result is visible.I can create the analysis pairs you like.

    It's fantastic, but I'll try to find an alternative to my approach, a new visual.

    I may be doing the wrong approach and going the hard way, but I can not see another way to show these intersections in a simple and flexible way.

    I'll accept suggestions.

    Thank you.



    Wednesday, January 16, 2019 10:10 AM
  • Hi  JoaquimLapa,

    So did Darren's suggestions help you? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 18, 2019 6:01 AM