Forum Discussion

jobf's avatar
jobf
Helper II
8 months ago
Solved

Avoid repeating data in the tables.

See the table below:

 

This is a table I created from some data from the visual. The first part consists of the data that was planned for each plot. The second part (from the "start of period" column onwards) represents the data that was executed in each plot.
As the executed data differs from the planned data (mainly in terms of variety), it can be seen that the planned data is repeated several times to cover the different data executed in the plot. I would like to know if there is any way to, instead of repeating them, put an empty space when the data is repeated.

The result would be something like this:

 

 

 

 

Need Help
  • srlabhe's avatar
    srlabhe
    8 months ago
    Create a new column or measure that conditionally returns a blank.
    • For a Table/Matrix: Use DAX to check against the previous row's value within the same group.
      dax
      Planned Data Display =
VAR CurrentPlot = 'YourTable'[PlotID]
VAR CurrentValue = 'YourTable'[Planned Data]
VAR PreviousValue =
    LOOKUPVALUE(
        'YourTable'[Planned Data],
        'YourTable'[PlotID], CurrentPlot,
        'YourTable'[Start of Period], EARLIER('YourTable'[Start of Period]) - 1 // Or similar logic to find previous row in group
    )
RETURN
    IF(CurrentValue = PreviousValue, BLANK(), CurrentValue)
      This gets complex with dates/sequences, a simpler check for immediate repeats is: IF(EARLIER('YourTable'[Planned Data]) = 'YourTable'[Planned Data], BLANK(), 'YourTable'[Planned Data]) if data is perfectly sorted. 

3 Replies

  • srlabhe's avatar
    srlabhe
    Super User
    8 months ago
    Create a new column or measure that conditionally returns a blank.
    • For a Table/Matrix: Use DAX to check against the previous row's value within the same group.
      dax
      Planned Data Display =
VAR CurrentPlot = 'YourTable'[PlotID]
VAR CurrentValue = 'YourTable'[Planned Data]
VAR PreviousValue =
    LOOKUPVALUE(
        'YourTable'[Planned Data],
        'YourTable'[PlotID], CurrentPlot,
        'YourTable'[Start of Period], EARLIER('YourTable'[Start of Period]) - 1 // Or similar logic to find previous row in group
    )
RETURN
    IF(CurrentValue = PreviousValue, BLANK(), CurrentValue)
      This gets complex with dates/sequences, a simpler check for immediate repeats is: IF(EARLIER('YourTable'[Planned Data]) = 'YourTable'[Planned Data], BLANK(), 'YourTable'[Planned Data]) if data is perfectly sorted. 
  • lbendlin's avatar
    lbendlin
    Super User
    8 months ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 months ago

    Hi,

    What happens when you prepare a matrix visual and drag all fields to the row well?