Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Filter Fact Value Table by multiple cluster se...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Filter Fact Value Table by multiple cluster segmentation

09-18-2021
03:06 AM

Hello everyone

I have this problem:

to each negotiation I have to assign a "score", based on the value of negotiention (ex. 150) and the date of the negotiation;

if it is included in the cluster (from 500 to 2000 score 1.00 - July 01 2020), but the cluster is updated every 6 months and I must not lose the history.

I have to keep the history of the clusters by appending them, because

a negotiation of 1000 in the first half of 2021 is worth score = 1.00

a negotiation of 1000 in the second half of 2021 is worth score = 0.00

__________________________________________________________________________

If I did not have the cluster of the first semester all the negotiations would have as score = 1.00

*IF I have only 1 cluster for all negotiation = ** VAR VALQUO = Fact_negotiation[Value negotiation]** VAR VALCLUSTER = FILTER(CLUSTER_SME_CONV, ** AND(CLUSTER[from] <= VALQUO, CLUSTER[to] >= VALQUO)) ** VAR Result= CALCULATE(DISTINCT( CLUSTER[Score]), VALCLUSTER)**return** Result*

________________________________________________________________

I am trying to create a calculated column that assigns the score to each negotiation, taking into account the value and date of the negotiation.

*(Format Date DAY / MONTH / YEAR)*

**Download:**

Grazie a tutti

Paol ·

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2021
11:37 AM

Thank you for the sample data. It really helps.

Ok, here is one way.

1) Create a Dimension table for the semesters with the function "New table" under Modeling in the ribbon using:

`Dim Score Period = DISTINCT('Date Table'[Sem])`

2) Now set up the model as follows:

3) In the Cluster table, add a new index column using:

```
Index =
VAR RNK =
RANKX (
FILTER ( ALL ( ClusterTable ), ClusterTable[HY] = EARLIER ( ClusterTable[HY] ) ),
[From],
,
DESC
)
RETURN
CALCULATE (
RNK,
ALLEXCEPT ( ClusterTable, ClusterTable[HY], ClusterTable[From] )
)
```

4) Create the following measures to calculate the score:

`Sum Result = SUM(ClusterTable[From])`

`Negotiation Value = SUM(FactTable[Value Negotiention])`

`Factor = SUM(ClusterTable[Score])`

```
Score =
IF (
ISINSCOPE ( 'Dim Score Period'[Sem] ),
IF (
ISBLANK ( [Negotiation Value] ),
BLANK (),
SWITCH (
TRUE (),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)
)
)
```

Now create the visual using the Semester field from the Dimension Score Period, and the rest from the fact table & add the score measure to get:

If you prefer to have the **Score as a column in the fact table**, you can use:

```
Score Column =
SWITCH (
TRUE (),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)
```

Caveat: you will have to tweak the [Score] measure/column if you have more than 5 score values in a semester

**I've attached a semaple PBIX file**

Proud to be a Super User!

Paul on Linkedin.

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2021
04:11 AM

Please share sample data (not an image) which you can paste from Excel into your post, or a PBIX file (which you can share the link via OneDrive, Deopbox....

Proud to be a Super User!

Paul on Linkedin.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2021
07:44 AM

HI, this here is the link (OneDrive), where there is an Excel file with two tables 1.Fact, 2.Cluster

I hope it works

Paolo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2021
11:37 AM

Thank you for the sample data. It really helps.

Ok, here is one way.

1) Create a Dimension table for the semesters with the function "New table" under Modeling in the ribbon using:

`Dim Score Period = DISTINCT('Date Table'[Sem])`

2) Now set up the model as follows:

3) In the Cluster table, add a new index column using:

```
Index =
VAR RNK =
RANKX (
FILTER ( ALL ( ClusterTable ), ClusterTable[HY] = EARLIER ( ClusterTable[HY] ) ),
[From],
,
DESC
)
RETURN
CALCULATE (
RNK,
ALLEXCEPT ( ClusterTable, ClusterTable[HY], ClusterTable[From] )
)
```

4) Create the following measures to calculate the score:

`Sum Result = SUM(ClusterTable[From])`

`Negotiation Value = SUM(FactTable[Value Negotiention])`

`Factor = SUM(ClusterTable[Score])`

```
Score =
IF (
ISINSCOPE ( 'Dim Score Period'[Sem] ),
IF (
ISBLANK ( [Negotiation Value] ),
BLANK (),
SWITCH (
TRUE (),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
[Negotiation Value]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)
)
)
```

Now create the visual using the Semester field from the Dimension Score Period, and the rest from the fact table & add the score measure to get:

If you prefer to have the **Score as a column in the fact table**, you can use:

```
Score Column =
SWITCH (
TRUE (),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 1 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 1 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 2 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 2 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 3 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 3 } ),
FactTable[Value Negotiention]
>= CALCULATE ( [Sum Result], ClusterTable[Index] IN { 4 } ), CALCULATE ( [Factor], ClusterTable[Index] IN { 4 } ),
CALCULATE ( [Factor], ClusterTable[Index] IN { 5 } )
)
```

Caveat: you will have to tweak the [Score] measure/column if you have more than 5 score values in a semester

**I've attached a semaple PBIX file**

Proud to be a Super User!

Paul on Linkedin.

Featured Topics

Top Solution Authors

User | Count |
---|---|

283 | |

155 | |

61 | |

55 | |

50 |

Top Kudoed Authors

User | Count |
---|---|

260 | |

171 | |

72 | |

66 | |

54 |