cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Partisan

## 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".

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
)
)
)
)

Thanks and Regards,
1 ACCEPTED SOLUTION
Solution Sage

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
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
8 REPLIES 8
Solution Sage

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
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
Super User

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" }
)
)
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Connect to me on LinkedIn || Need consulting? Hire me for a Power BI gig on UpWork.
Learn with me on YouTube @PowerBITambayan.
Post Partisan

Hi @danextian,

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

Any suggestion?

Thanks and Regards,

Solution Sage

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
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
Post Partisan

Hi @AmiraBedh,

Any suggestion how to do it?

Thank and Regards,

Solution Sage

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
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
Post Partisan

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.

Thanks and Regards,

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))
)
)
)
)
)

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors