Forum Discussion

UDMH's avatar
UDMH
Frequent Visitor
1 year ago
Solved

Grouping and unique values

Hi All, This is my requirement, help me on this. Whether we can achieve this in Power BI. This is my data for example, Code Name Description Text Amount 1 AAA     120 1 A...
Tips and Tricks
  • Irwan's avatar
    Irwan
    1 year ago

    hello UDMH 

     

    please check if this accomodate your need.

     

    create new table with following DAX.

    Summarize =
    SUMMARIZE(
        FILTER(
            'Table',
            'Table'[Description]<>BLANK()&&
            'Table'[Text]<>BLANK()
        ),
        'Table'[Code],
        'Table'[Name],
        'Table'[Description],
        'Table'[Text],
        "Amount",
        CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Code]))
    )

     


     

    Hope this will help.

    Thank you.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    One of ways to create a new table is writing DAX formula something like below.

    Please check the below picture and the attached pbix file.

     

     

    expected result table = 
	SUMMARIZECOLUMNS(
		Data[Code],
		Data[Name],
		Data[Description],
		Data[Text],
		FILTER(
			Data,
			Data[Name] <> BLANK() && Data[Description] <> BLANK() && Data[Text] <> BLANK()
		),
		"@Amount", CALCULATE(
			SUM(Data[Amount]),
			ALLEXCEPT(
				Data,
				Data[Code]
			)
		)
	)

     

  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi UDMH ,
    You could try the below simple version using Dax:

    NewTable = 
SUMMARIZE(
    'Table',
    'Table'[Code],
    "Name", MAX('Table'[Name]),
    "Description", MAX('Table'[Description]),
    "Text", MAX('Table'[Text]),
    "Amount", SUM('Table'[Amount])
)

     

    Also, you could use power query group by transformation. See images below:

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

    Best Regards,

    Shahariar Hafiz

divyed's avatar
divyed
Super User
1 year ago

Hello UDMH ,

 

Here is the dax based on your data.

// I have created a sample table named Sample_Table with 4 columns Code_Name, Description, Text and Amount.

//You can make use of Summerize function to achieve the same. 

SummarizedTable =
SUMMARIZE(
    Sample_Table,                         // Replace with your table name
    Sample_Table[Code_Name],                   // Group by Code
    "Description",
        MAXX(
            FILTER(Sample_Table, Sample_Table[Description] <> BLANK()), Sample_Table[Description]   // Get first non-blank Description
        ),
    "Text",
        MAXX(
            FILTER(Sample_Table, Sample_Table[Text] <> BLANK()), Sample_Table[Text]   // Get first non-blank Text
        ),
    "Total Amount",
        SUM(Sample_Table[Amount])          // Sum of Amount
)
 

 

 
I hope this will solve your problem.
 
Please mark this as solution if it has solved your problem. I appreciate your Kudos :).
 
Cheers