Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ADSL
Post Prodigy
Post Prodigy

Calculate Visited Frequency with exclude some conditions

Hi BI Community Team,

 

I have a data table of visited and applied a measure to caluclate the visited frequency as below.

 

But we want to be exclude and not calculate if value from customer code are: "1234".

 

Sample file: https://drive.google.com/drive/folders/1WzBr8h-QQFbZQf1XcdQH3VUBCErlxUAd?usp=sharing 

 

Here is the measure:

 

Cnt_Visit =
SUMX(
    VALUES(Visited_Freq[Frequency]),
    Var _cnt = CALCULATE(MAX(Visited_Freq[Index]))
    Return
        SUMX(
            VALUES(Data[SR_CODE]),
            COUNTROWS(
                FILTER(
                    VALUES(Data[CUSTOMER_CODE]),
                Var _dc =
                    CALCULATE(DISTINCTCOUNT(Data[CALL_DATE]))
                Return
                _dc = _cnt || _cnt >8 && _dc >8
                )
            )
        )
)
 
Any suggestion/advise?
 
Thanks and Regards,
1 ACCEPTED SOLUTION
AmiraBedh
Resident Rockstar
Resident Rockstar

After we discussed your problem, you can apply a filter directly to the visual.

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

8 REPLIES 8
AmiraBedh
Resident Rockstar
Resident Rockstar

After we discussed your problem, you can apply a filter directly to the visual.

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
danextian
Super User
Super User

Hi @ADSL 

Try these although the results are the same as your original formula as the values you want to be ecxluded from customer code are not there to begin with.

=
SUMX (
    VALUES ( Visited_Freq[Frequency] ),
    VAR _cnt =
        CALCULATE ( MAX ( Visited_Freq[Index] ) )
    RETURN
        SUMX (
            VALUES ( Data[SR_CODE] ),
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        FILTER ( data, NOT Data[CUSTOMER_CODE] IN { "9999", "29100", "1075" } ),
                        Data[CUSTOMER_CODE]
                    ),
                    VAR _dc =
                        CALCULATE ( DISTINCTCOUNT ( Data[CALL_DATE] ) )
                    RETURN
                        _dc = _cnt
                            || _cnt > 8
                            && _dc > 8
                )
            )
        )
)
=
SUMX (
    VALUES ( Visited_Freq[Frequency] ),
    VAR _cnt =
        CALCULATE ( MAX ( Visited_Freq[Index] ) )
    RETURN
        SUMX (
            VALUES ( Data[SR_CODE] ),
            COUNTROWS (
                FILTER (
                    VALUES ( Data[CUSTOMER_CODE] ),
                    VAR _dc =
                        CALCULATE ( DISTINCTCOUNT ( Data[CALL_DATE] ) )
                    RETURN
                        ( _dc = _cnt
                            || _cnt > 8
                            && _dc > 8 )
                            && NOT Data[CUSTOMER_CODE] IN { "9999", "29100", "1075" }
                )
            )
        )
)

 

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian,

It's still be included after trying your measure feedback, and as the screenshot below.

 

Any suggestion?

 

2023-08-06_21-06-25.png

 

Thanks and Regards,

AmiraBedh
Resident Rockstar
Resident Rockstar

I checked the pbix file and I see that the Data table and the visited_freq are linked ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi @AmiraBedh,

Yes, it's not linked because I don't know how to link.

 

Any suggestion how to do it?

 

Thank and Regards,

AmiraBedh
Resident Rockstar
Resident Rockstar

How did you generate your visual ? You need to have a column in DATA table (I am assuming it is your fact table) to link it with the visited_freq.

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Hi @AmiraBedh,

 

In Data table, it doesn't have the column of visited_freq. we just created the measure to calculate it.

For visualization, we just drag and drop as you see the sample file sharing. But I remember that it can be working and showing even it have no relationship when tried a few time.

 

Could you please help to make it for this case? Thank you in advance.

 

Thanks and Regards,

anilkapkoti
Helper II
Helper II

Cnt_Visit2 =
SUMX(
    VALUES(Visited_Freq[Frequency]),
    VAR _cnt = CALCULATE(MAX(Visited_Freq[Index]))
    RETURN
        SUMX(
            VALUES(Data[SR_CODE]),
            COUNTROWS(
                FILTER(
                    VALUES(Data[CUSTOMER_CODE]),
                    VAR _dc = CALCULATE(DISTINCTCOUNT(Data[CALL_DATE]))
                    RETURN
                        NOT (
                            Data[CUSTOMER_CODE] IN {"9999", "29100", "1075"}
                            || (_dc = _cnt || (_cnt > 8 && _dc > 8))
                        )
                )
            )
        )
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors