Forum Discussion

KevinSV's avatar
KevinSV
Icon for Helper II rankHelper II
1 year ago
Solved

Filtrer in one line

Hello everyone,  I need your help on my Power BI Desktop.  First of all, i import my data from a SQL Server for my report. I put some slicers for filter and give a table after the filtrer.  In thi...
Need Help
  • KevinSV's avatar
    KevinSV
    1 year ago

    Here is the update of the topic : 
    So i modify my code : 

    CombinedTable2 =

    UNION(

    SELECTCOLUMNS(

        SUMMARIZE(

            FILTER(ALL('PCA'); 'PCA'[IsAggregateRow] = 1);

            'PCA'[SOCIETE]; PCA[CJOURNAL]; PCA[DTECRIT]; PCA[ANALYT]; --here is the modify i add all the column i need on this line of the code

            "TCOMPTE"; "48700000";

            "MDEBITS"; [Total_MDEBITS];

            "DCREDITS"; [Total_DCREDITS];

            "TLIB"; SELECTEDVALUE(PCA[LIB2])

        );

    "CJOURNAL"; PCA[CJOURNAL];

    "TCOMPTE"; "48700000";

    "CSOC";'PCA'[SOCIETE];

    "MDEBITS"; [Total_MDEBITS];

    "DCREDITS"; [Total_DCREDITS];

    "DECRIT"; PCA[DTECRIT];

    "CANALYT3"; 'PCA'[ANALYT];

    "TLIB"; SELECTEDVALUE(PCA[LIB2])

    );

    SELECTCOLUMNS(

        FILTER(ALL('PCA'); LEFT('PCA'[TCOMPTE]; 1) = "7");

        "CJOURNAL"; 'PCA'[CJOURNAL];

        "TCOMPTE"; 'PCA'[TCOMPTE];

        "CSOC"; 'PCA'[SOCIETE];

        "MDEBITS"; 'PCA'[DEBITS];

        "DCREDITS"; 'PCA'[CREDITS];

        "DECRIT"; 'PCA'[DTECRIT];

        "CANALYT3"; 'PCA'[ANALYT];

        "TLIB"; 'PCA'[LIB2]

    ))


     


    Thanks again  for your helping ! 

KevinSV's avatar
KevinSV
Icon for Helper II rankHelper II
1 year ago

Hello everyone, so i'm coming with you about this topic. 
I try to add and modify your code and i am on a good way about what i want (Thanks again) : 

CombinedTable2 =
    VAR CJOURNAL_UNIQUE = SELECTEDVALUE('PCA'[CJOURNAL])
    VAR DECRIT_UNIQUE = SELECTEDVALUE('PCA'[DTECRIT])
    VAR CANALYT3_UNIQUE = SELECTEDVALUE('PCA'[ANALYT])

    RETURN
UNION(
SELECTCOLUMNS(
    SUMMARIZE(
        FILTER('PCA'; 'PCA'[IsAggregateRow] = 1);
        'PCA'[SOCIETE];
        "TCOMPTE"; "48700000";
        "CJOURNAL"; CJOURNAL_UNIQUE;
        "MDEBITS"; [Total_MDEBITS];
        "DCREDITS"; [Total_DCREDITS];
        "DECRIT"; DECRIT_UNIQUE;
        "CANALYT3"; CANALYT3_UNIQUE;
        "TLIB"; SELECTEDVALUE(PCA[LIB2])
    );
"CJOURNAL"; SELECTEDVALUE(PCA[CJOURNAL]);
"TCOMPTE"; "48700000";
"CSOC";'PCA'[SOCIETE];
"MDEBITS"; [Total_MDEBITS];
"DCREDITS"; [Total_DCREDITS];
"DECRIT"; SELECTEDVALUE('PCA'[DTECRIT]);
"CANALYT3"; SELECTEDVALUE('PCA'[ANALYT]);
"TLIB"; SELECTEDVALUE(PCA[LIB2])
);
SELECTCOLUMNS(
    FILTER('PCA'; LEFT('PCA'[TCOMPTE]; 1) = "7");
    "CJOURNAL"; 'PCA'[CJOURNAL];
    "TCOMPTE"; 'PCA'[TCOMPTE];
    "CSOC"; 'PCA'[SOCIETE];
    "MDEBITS"; 'PCA'[DEBITS];
    "DCREDITS"; 'PCA'[CREDITS];
    "DECRIT"; 'PCA'[DTECRIT];
    "CANALYT3"; 'PCA'[ANALYT];
    "TLIB"; 'PCA'[LIB2]
))

The problem is : I want to add the Cjournal and DECRIT values on the line (there are the same for all the lines) : 

That is the result i got when i only filtrer by the CSOC but when i add another filtrer on the DECRIT for example, the first line will disapear because he is blank. And my code didn't work on adding the value with the selectedvalue. 
Do you have any ideas ? 
Thanks you for helping ! 





  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi KevinSV - To ensure that values for CJOURNAL and DECRIT remain visible across all rows, you can try adjusting your DAX code by using ALL or ALLEXCEPT to ignore specific filters, so these columns retain their values irrespective of filtering on other columns.

    CombinedTable2 =
    VAR CJOURNAL_UNIQUE = SELECTEDVALUE('PCA'[CJOURNAL])
    VAR DECRIT_UNIQUE = SELECTEDVALUE('PCA'[DTECRIT])
    VAR CANALYT3_UNIQUE = SELECTEDVALUE('PCA'[ANALYT])

    RETURN
    UNION(
    SELECTCOLUMNS(
    SUMMARIZE(
    FILTER(ALL('PCA'), 'PCA'[IsAggregateRow] = 1),
    'PCA'[SOCIETE],
    "TCOMPTE", "48700000",
    "CJOURNAL", CJOURNAL_UNIQUE,
    "MDEBITS", [Total_MDEBITS],
    "DCREDITS", [Total_DCREDITS],
    "DECRIT", DECRIT_UNIQUE,
    "CANALYT3", CANALYT3_UNIQUE,
    "TLIB", SELECTEDVALUE(PCA[LIB2])
    ),
    "CJOURNAL", CJOURNAL_UNIQUE,
    "TCOMPTE", "48700000",
    "CSOC", 'PCA'[SOCIETE],
    "MDEBITS", [Total_MDEBITS],
    "DCREDITS", [Total_DCREDITS],
    "DECRIT", DECRIT_UNIQUE,
    "CANALYT3", CANALYT3_UNIQUE,
    "TLIB", SELECTEDVALUE(PCA[LIB2])
    ),
    SELECTCOLUMNS(
    FILTER(ALL('PCA'), LEFT('PCA'[TCOMPTE], 1) = "7"),
    "CJOURNAL", CJOURNAL_UNIQUE,
    "TCOMPTE", 'PCA'[TCOMPTE],
    "CSOC", 'PCA'[SOCIETE],
    "MDEBITS", 'PCA'[DEBITS],
    "DCREDITS", 'PCA'[CREDITS],
    "DECRIT", DECRIT_UNIQUE,
    "CANALYT3", CANALYT3_UNIQUE,
    "TLIB", 'PCA'[LIB2]
    )
    )

    Please check the above modified one ,Hope this approach should help keep the values consistent in your table