DAX so that they can be filtered on categories

[neelofarshama]

Hi All,

I have a scenario where  a DAX formulas for Site Conversions and Conv %, so that they can be filtered based on Property and Date, but nothing else (though Total Conversions needs to be filtered based on all filters).

Total Conversions=DISTINCTCOUNT(Table1[WEB_ID])

site Conversions=Table1[conv]

Conv %(of Site)=Total Conversions/sum(site Conversions).

I want Site Conversions and Conv % to only change when date and Property filter are changed else they shoukd remain unchanged.

Please Advice.

Thanks In Advance,

Neelofar.

[v-yuaj-msft]

Hi @neelofarshama ,

Based on your description, you can modify your DAX function a little.

I create some measures as follows.

Total Conversions =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[WEB_ID] ),
    ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Property] )
)

site Conversions =
CALCULATE (
    SUM ( 'Table'[conv] ),
    ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Property] )
)

Conv %(of Site) =
DIVIDE ( [Total Conversions], [site Conversions] )

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[neelofarshama]

Hi,

Thank you for the reply but my requirement is little different from what you have explained. I have created a measure for Site Conversions called New Site Conv, now here I dont want this measure to get affected with slicers "Category" and "Mailing Name". I used ALL() function but still I am unable to achiveve the results.

New Site Conv = calculate(sum(DAILY_SITE_CONVERSIONS[CONVERSIONS]),ALLEXCEPT(SFMC_SENDS,SFMC_SENDS[Property],SFMC_SENDS[Send Date]),ALL(SFMC_SENDS[Category]),All(SFMC_SENDS[MailingName])).

The above sccreenshot shows when no slicer is selected the value is 12210. The below screenshot shows when category slicer is selected the value of New Site Conversion is changing which is not acceptable.

Please suggest.

[v-yuaj-msft]

Hi @neelofarshama ,

Based on your description, do you use the field of one table to filter the other table?

This formula won't work. What's the relationship between two tables? Is there a unique key to connect two tables? The structure of your model is not clear to me.

Could you please share some sample data and the expected result to have a clear understanding of your question? Screenshots are also welcome. I can do some tests for you.

You can save your files in some cloud sharing platforms and share the link here.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Yuna

[neelofarshama]

Yes, I am using one field from one table to filter other table. Also sharing the expected results below 

when all the slicers are selcted as above the Site conversions and Con% are correct.

When the slicers "Property" or "Date" is selcted the Site Conversion should change based on the filter selected. The new measure created "New Site Conversion" is also changing so results are fine till here.

When slicer "Category" is selected I do not want my "New Conversion" to change it should remain same as "Site Conversion"(12,210) but its chaing to 5347 despite using all() function.

[Anonymous]

Hey @neelofarshama ,

Just wondering whether you have already edited interactions to make the New Site Conversions card not respond to Category and Mailing Name slicer visuals?

[neelofarshama]

Hi,

I do not want to edit interactions feature so I am trying to write DAX for it. I have used ALL(), Removefilter(), Allexpect() functions but could not achieve the result.

If I use edit interactions for "New Conversion" the %Conv will give me wrong values as %Conv=Total Converions/Site Conversion.

[Anonymous]

Hi @neelofarshama ,

I have tried a few different functions but am unable to get what you're after. 

However I can see in your data model that:

1. All your tables are set to cross-filter in both directions and one of them is a many-to-many relationship;

2. There are no dimension tables;

3. Site Conversion is an implicit measure (using the default summarization of the conversions column)

May I suggest creating some dimension tables (Property, Category, Date) and considering whether you could work with single-direction filter between the tables instead as a start? It might be possible that with some tweaks to your data model, one of the DAX formulas suggested will give the result you're after.

[neelofarshama]

Hi @Anonymous ,

Can you please suggest me how can I modigy my model to achieve this result as I am new to Power BI and model designing is smothing which I am not much aware of.

Please suggest me on how an ideal model should be and how can I change this exisiting model.

[Anonymous]

Hi @neelofarshama ,

I've made a few changes to your data model and updated the slicers, please see file here:

Emails and Messaging Reporting date fix 20210511.pbix (dropbox.com)

Not sure whether you still need the New Site Conversion card, but the ALL() on Category and Mailing Name has no effect on the calculation anymore as those two tables have no relationship with the Daily Site Conversion table in my version of the model.

I've mainly tried to model it in a star schema, with the exception of Date and Property having connecting to two tables solely for the Site Conversions card. Property table is an intermediate table that allows filter in both directions to the Daily Site Conversions but is only one-to-many to SFMC_Sends. This prevents Category filtered data from flowing back to the Property from SFMC_Sends and interfering with Site Conversion card (which was what was happening in the original data model due to the bi-direction relationship). There are still a few more tweaks I would make but I'm not familiar with your data set and what you need to achieve so I have stopped here.

The model view looks a bit complicated because there are two fact tables that require relationships with the dimension tables. I would suggest picking out the columns you need from the Newsletter table and put them into SFMC_Sends (using PowerQuery or doing it at source before Import to PBI), since SFMC_Sends already has Category, Property, Mailing Name in there. That way you only have one fact table (which is SFMC_Sends) and Date, Category and Mailing Name tables only need to be connected to one table.

Also, the new dimension tables I added only have one column. Ordinarily you would have a few more columns with other attributes. But if there aren't any that is required, it is fine to leave the dimension tables this way as columns take up more space than rows and makes the model slower. If you are creating more KPI's that are based on week/month/year, it is worth expanding the Date table to contain week/month/year columns so you can add the WoW/MoM/YoY measures.

This article here explains star schema data modelling quite well: Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

This article here shows how to create a simple date table: Creating a simpler and chart-friendly Date table in Power BI - SQLBI

Hope the above is useful. Please let me know whether it worked and if you need more help.

Cheers

[v-yuaj-msft]

Hi @neelofarshama ,

Based on your sample .pbix file, I cannot reproduce your problem.  "New Conversion" doesn't change if I filter on "category" or "email" or "mailing name".

Best Regards,

Yuna

[neelofarshama]

Hi @v-yuaj-msft ,

Please find the updated link

https://caltimes-my.sharepoint.com/:u:/g/personal/neelofer_shama_latimes_com/EQYEOuQ4mhxIppKgz4qQNJo...

[neelofarshama]

Hi @v-yuaj-msft ,

Please find the link of the file

https://caltimes-my.sharepoint.com/:u:/g/personal/neelofer_shama_latimes_com/EViGyQNO2tFAo6Z5FoUG9J0...

[neelofarshama]

Hi @v-yuaj-msft ,

I have tried the DAX you have 

New Site Conv = calculate(sum(DAILY_SITE_CONVERSIONS[CONVERSIONS]),ALLEXCEPT(SFMC_SENDS,SFMC_SENDS[Property],SFMC_SENDS[Send Date]))

this is working fine as expected(chaging when I am selecting filters of Date and Property)but the problem is arising when I am trying to select filters from "Category" and "Mailing Name",the card "New Site Conversion" is also changing which is not what I am looking for. The card should not be effected with any selections made on Category and Mailing name slicers.

When Category is selcted to "Ad Mail" the New Site Conversions is chaning  to 2033 it shiuld remain 12210.

Please help.

[selimovd]

Hey @neelofarshama ,

are the other filters like "Category" and "Welcome Email" in the same table as "Property"?

If yes, then you should just remove all filters except for date and property. Try this measure:

Conv %(of Site) =
DIVIDE(
    [Total Conversions],
    CALCULATE(
        SUM( myTable[site Conversions] ),
        ALLEXCEPT(
            dateTable,
            dateTable[Date]
        ),
        ALLEXCEPT(
            propertyTable,
            propertyTable[Property]
        )
    )
)

If they are in separate tables you have to remove the filters with ALL(CategoryTable) etc.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[neelofarshama]

Hi @Denis,

Thank you for the reply, yes all the filters are in the same table.

I have applied the DAX which you have suggested but the results are not as expected.

• Property = All
• Category = All
• Welcome Email = False
• Mailing Name = Essential California
• Date = 3/1/21-3/31/21

Should return: 73 Total Conversions, 12,210 Site Conversions, 0.6% Test Conv % (of Site)

Please help.